﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;

namespace GroupProject
{
    public partial class frmShippers : Form
    {
        SqlConnection con;
        SqlDataAdapter dadap;
        DataSet dset;
        DataTable dtable ;
        //SqlCommandBuilder cbuild;
        //DataRow drow;
       DataView dview;
        SqlCommand cmd;
        const string connectionstr = @"Data Source=(local);Initial Catalog=TSQLFundamentals2008;Persist Security Info=True;User ID=sa;Password=m1ch@3ly1428";
        public frmShippers()
        {
            InitializeComponent();
            initializeAOObject();
            loadData();
        }
        void initializeAOObject()
        {
            con = new SqlConnection();
            con.ConnectionString = ConfigurationManager.ConnectionStrings["SqlStr"].ConnectionString;
            con.Open();
            cmd = new SqlCommand(); 
            cmd.Connection = con;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "sp_getShipper";
            dadap = new SqlDataAdapter(cmd);
           // cbuild = new SqlCommandBuilder(dadap);
            dset = new DataSet();
            dadap.FillSchema(dset,SchemaType.Mapped);
            dadap.Fill(dset, "Sales.Shippers");
         

        }
        #region Coonect_layer
        void loadData()
        {
            //try
            //{
            //    initializeAOObject();
            //    cmd.CommandText = "SELECT * FROM Sales.Shippers";
            //    SqlDataReader dr = cmd.ExecuteReader();
            //    gvShipper.Rows.Clear();
            //    while (dr.Read())
            //    {
            //        gvShipper.Rows.Add(dr[0].ToString(), dr[1].ToString(), dr[2].ToString());
            //    }
            //    dr.Close();
            //}
            //catch (Exception ex)
            //{
            //    MessageBox.Show(ex.Message);
            //}
            //finally
            //{
            //    con.Close();
            //}
            dtable = dset.Tables["Sales.Shippers"];
            dview = new DataView(dtable);
            gvShipper.DataSource = dtable;

        }
        //void insertShipper()
        //{
        //    try
        //    {
        //        initializeAOObject();
        //        cmd.CommandType = CommandType.StoredProcedure;
        //        cmd.CommandText = "sp_InsertShipper";
        //        SqlParameter para = new SqlParameter("@companyname", SqlDbType.NVarChar);
        //        para.Size = 40;
        //        para.Value = txtCompName.Text;
        //        cmd.Parameters.Add(para);

        //        para = new SqlParameter("@phonenumber", SqlDbType.NVarChar);
        //        para.Size = 24;
        //        para.Value = txtPhone.Text;
        //        cmd.Parameters.Add(para);
        //        cmd.ExecuteNonQuery();

        //    }
        //    catch (Exception ex)
        //    {
        //        MessageBox.Show(ex.Message);
        //    }
        //    finally
        //    {
        //        con.Close();
        //    }
        //}
        void Updateshipper()
        {
            try
            {
                initializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_UpdateShipper";
                SqlParameter para = new SqlParameter("@shipperID", SqlDbType.Int);
                para.Value = txtID.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@companyname", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtCompName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@phonenumber", SqlDbType.NVarChar);
                para.Size = 24;
                para.Value = txtPhone.Text;
                cmd.Parameters.Add(para);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show("Failed!");
            }
            finally
            {
                con.Close();
            }


        }
        void deleteShipper()
        {
            try
            {
                initializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_DeleteShipper";
                SqlParameter para = new SqlParameter("@shipperID", SqlDbType.Int);
                para.Value = txtID.Text;
                cmd.Parameters.Add(para);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show("Failed!");
            }
            finally
            {
                con.Close();
            }
        }
        void searchbyID()
        {

            
            try
            {
                dview = new DataView(dtable);
                dview.RowFilter = string.Format("shipperid = {0}", txtSearch.Text);
                gvShipper.DataSource = dview;
            }
                
            catch (Exception )
            {
                MessageBox.Show("ID "+txtSearch.Text+" is not exist");
                
            }
            

          

        }
        void searchbyName()
        {

            try
            {
                dview = new DataView(dtable);
                dview.RowFilter = string.Format("companyname like '{0}%'", txtSearch.Text.Trim());
                gvShipper.DataSource = dview;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

     

        }
        bool valid()
        {
            bool valid = true;
            if (txtCompName.Text.Length == 0)
            {
                errorProvider1.SetError(txtCompName, "Company Name cannot empty");
                valid = false;
            }
            if (txtPhone.Text.Length == 0)
            {
                errorProvider1.SetError(txtPhone, "Phone cannot be empty");
                valid = false;
            }
           
            if (valid == false)
            {
                return false;
            }
            return true;
        } 
        #endregion

        void insertShipperdis()
        {
            try
            {
                initializeAOObject();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "sp_InsertShipper";
                SqlParameter para = new SqlParameter("@companyname", SqlDbType.NVarChar);
                para.Size = 40;
                para.Value = txtCompName.Text;
                cmd.Parameters.Add(para);

                para = new SqlParameter("@phonenumber", SqlDbType.NVarChar);
                para.Size = 24;
                para.Value = txtPhone.Text;
                cmd.Parameters.Add(para);
                dadap = new SqlDataAdapter(cmd);
                dset = new DataSet();
                dadap.FillSchema(dset, SchemaType.Mapped);
                dadap.Fill(dset, "Sales.Customers");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                MessageBox.Show("Failed!");
            }
           

        }
        private void btnInsert_Click(object sender, EventArgs e)
        {
            if (valid())
            {
                insertShipperdis();
                initializeAOObject();
                loadData();
                MessageBox.Show("Insert successful!");
            }
        }

        private void gvShipper_SelectionChanged(object sender, EventArgs e)
        {
            try
            {
                if (gvShipper.SelectedCells.Count > 1)
                {
                    DataGridViewRow r = gvShipper.SelectedRows[0];
                    txtID.Text = r.Cells[0].Value.ToString();
                    txtCompName.Text = r.Cells[1].Value.ToString();
                    txtPhone.Text = r.Cells[2].Value.ToString();
                }
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message);
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            if (valid())
            {
                Updateshipper();
                MessageBox.Show("Update successfull!");
                initializeAOObject();
                loadData();
            }
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Are you sure to Delete?","Warning",MessageBoxButtons.YesNo,MessageBoxIcon.Question)==DialogResult.Yes)
            {
                deleteShipper();
                initializeAOObject();
                loadData();
                MessageBox.Show("Delete Complete");
            }
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (cbbOption.Text == "ID")
            {
                searchbyID();
            }
            if (cbbOption.Text == "COMPANY NAME")
            {
                searchbyName();
            }
        }

        private void btnReload_Click(object sender, EventArgs e)
        {
            initializeAOObject();
            loadData();
            clear();
        }

        private void frmShippers_Load(object sender, EventArgs e)
        {

        }
        public void clear()
        {
            txtID.Clear();
            txtPhone.Clear();
            txtCompName.Clear();
            txtSearch.Clear();
            errorProvider1.Clear();

        }

        private void btnNew_Click(object sender, EventArgs e)
        {
            clear();
        }

        private void gvShipper_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }
    }
}
